package com.api.gbhg.liwenjun;


import com.alibaba.fastjson.JSON;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetDataSource;
import weaver.conn.RecordSetTrans;

import java.util.*;

public class VersionFlowUtil {



    private static  String versionTableName = "uf_jingfei_version";//经费版本主表


    /**
     * 同步流程数据到版本表
     * @param requestId 请求id
     * @param workFlowId 流程配置id (对应workflow_base表中id字段)
     * @param tableEnum 流程对应枚举
     * @param specialMapping 流程抽象表与流程表的字段映射关系
     */
    public static  boolean sysDataToVersion(String requestId, String workFlowId, GBHGConstant.FlowVersionTableEnum tableEnum, Map<String, String> specialMapping) {
        int mainTableId = getFlowTableLableId(workFlowId);
        //根据流程配置id获取对应的表名
        String tableName = getFlowTableName(workFlowId);

        RecordSetTrans flowData = new RecordSetTrans();
        try {
            flowData.executeQuery("select * from " + tableName + " where requestId=" + requestId);
            flowData.next();
            Vector vector = flowData.getArray();
            Object[] valus = (Object[]) Arrays.stream(vector.toArray()).findFirst().orElse(null);
            String[] colums = flowData.getColumnName();

            updateMainVersion(flowData,requestId,workFlowId, valus,colums, specialMapping,tableEnum);
            //更新流程表数据
            updateFlowVersion(flowData,requestId,tableEnum.getFlowVersionTableName(),valus,colums);
            int flowVesionTableId = getFlowTableIdByName(tableEnum.getFlowVersionTableName());

            flowData.execute("select id from "+tableEnum.getFlowVersionTableName()+" where requestId="+requestId);
            flowData.next();
            int mainId= flowData.getInt("id");

            //更新明细表到版本流程明细表
            List<String> flowDetailTableNames = getDetailTables(mainTableId);
            for(String tbname:flowDetailTableNames){
                String tbDetailName=tableEnum.getFlowVersionTableName()+"_dt"+tbname.substring(tbname.length()-1);
                //检查表是否存在
                flowData.execute("select OBJECT_ID(N'"+tbDetailName+"',N'U')");
                String tableRes= ((Object[])flowData.getArray().get(0))[0]+"";
                if(tableRes==null||tableRes.isEmpty()){
                    continue;
                }
                flowData.execute("delete from "+tbDetailName+" where mainid="+mainId);
                List<String> tbDetailNameFields=getTableFileds(flowVesionTableId,tbDetailName);
                String insertField=joinStringBySplit(tbDetailNameFields,",");
                flowData.execute("insert into "+tbDetailName +" (mainid,"+insertField+") select "+mainId+" as mainId,"+insertField+" from "+tbname +" where mainid="+valus[0]);
            }
            return true;
        }catch (Exception ex){
            ex.printStackTrace();
            flowData.rollback();
        }
        return false;
    }

    /**
     * 同步主版本抽象表数据
     * @param requestId 请求记录id
     * @param valus 当前流程主表值列表
     * @param colums 当前流程表字段列表
     * @param specialMapping 不对应字段的映射关系
     * @return
     */
    public static  boolean updateMainVersion(RecordSetTrans flowData , String requestId,String workFlowId,Object[] valus ,String[] colums, Map<String, String> specialMapping,GBHGConstant.FlowVersionTableEnum tableEnum) throws Exception {
        if(specialMapping==null){
            specialMapping=new HashMap<>();
        }
        specialMapping.put("lcjlsqjlid","requestid");
        specialMapping.put("dlhsdw","szdlhsdw");//huangwm
        int versionTableId = getFlowTableIdByName(versionTableName);
        List<String> versionTableFields = getTableFileds(versionTableId);
        versionTableFields.add(0,"requestid");
        Object[] values = getDataArray(versionTableFields, valus, colums, specialMapping).toArray();//流程数据值数组
        System.err.println("流程数据值数组");
        System.err.println(JSON.toJSONString(versionTableFields));
        System.err.println(JSON.toJSONString(valus));
        System.err.println(JSON.toJSONString(colums));
        System.err.println(JSON.toJSONString(values));
        flowData.executeQuery("select * from " + versionTableName + " where lcjlsqjlid=" + requestId);
        String sql = null;
        if (!flowData.next()) {//判断是否有记录
            //首次申报
            sql = getInsertSql(versionTableFields, versionTableName);
        } else {
            //更新申报
            sql = getUpdateSql(versionTableFields, versionTableName," where lcjlsqjlid=" + requestId);
        }

        flowData.executeUpdate(sql, values);
        String[] nodeNameInfo= getFlowNodeName(requestId);
        flowData.execute("update "+versionTableName+" set lcid="+workFlowId+",lcbj='"+tableEnum.getFlowType()+"',lczt='"+nodeNameInfo[1]+"' ,lcztz="+nodeNameInfo[0]+" where lcjlsqjlid=" + requestId);
        //设置当前为最新版本，其余的不可用版本
        //flowData.execute("update uf_jingfei_version set sfky=0  where ysnd='"+values[5]+"' and sbdw='"+values[13]+"' and lcbj='"+tableEnum.getFlowType()+"'");
        flowData.execute("update uf_jingfei_version set sfky=1 where requestId='"+requestId+"'");
        return true;
    }

    /**
     *更新流程版本表
     * @param requestId  请求记录id
     * @param flowVesionTableName 流程表表名
     * @param valus 当前流程主表值列表
     * @param colums 当前流程表字段列表
     * @return
     */
    public static  boolean updateFlowVersion(RecordSetTrans flowData ,String requestId, String flowVesionTableName,Object[] valus ,String[] colums) throws Exception {
        int tableId = getFlowTableIdByName(flowVesionTableName);
        List<String> tableFields = getTableFileds(tableId);
        tableFields.add(0,"requestid");
        flowData.executeQuery("select * from " + flowVesionTableName + " where requestid=" + requestId);
        String sql = null;
        if (!flowData.next()) {//判断是否有记录
            //首次申报
            sql = getInsertSql(tableFields, flowVesionTableName);
        } else {
            //更新申报
            sql = getUpdateSql(tableFields, flowVesionTableName," where requestId=" + requestId);
        }
        Object[] values = getDataArray(tableFields, valus, colums, null).toArray();//流程数据值数组
        return flowData.executeUpdate(sql, values);
    }


    /**
     * 为表字段数组取值
     * @param tableFields 表字段列表
     * @param valus 值列表
     * @param colums 列列表
     * @param specialMapping 字段映射关系map
     * @return
     */
    public static  List<Object> getDataArray(List<String> tableFields, Object[] valus, String[] colums, Map<String, String> specialMapping) {
        List<Object> obj = new ArrayList<>();
        for (String c : tableFields) {
            String cmName = c;
            if (specialMapping!=null&& specialMapping.containsKey(c)) {
                cmName = specialMapping.get(c);
            }
            obj.add(getObjValueByIndex(valus, colums, cmName));
        }
        return obj;
    }

    /**
     * 根据列的位置获取对应值，未找到的返回null
     * @param valus 值列表
     * @param colums 列列表
     * @param key 需要取值的列名
     * @return
     */
    public static  Object getObjValueByIndex(Object[] valus, String[] colums, String key) {
        for (int i = 0; i < colums.length; i++) {
            if (colums[i].toLowerCase().equals(key.toLowerCase())) {
                if(valus[i] instanceof String &&((String) valus[i]).isEmpty()){
                    return null;
                }
                return valus[i];
            }
        }
        return null;
    }

    /**
     * 根据表名，表字段生成sql插入语句
     * @param tableFields
     * @param tableName
     * @return
     */
    public static  String getInsertSql(List<String> tableFields, String tableName) {
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("insert into " + tableName + " (");
        stringBuffer.append(joinStringBySplit(tableFields, ","));
        stringBuffer.append(") values (");
        stringBuffer.append(getQuestionMark(tableFields.size()));
        stringBuffer.append(")");
        return stringBuffer.toString();
    }

    /**
     * 根据表明，表字段生成updatesql
     * @param tableFields
     * @param tableName
     * @return
     */
    public static  String getUpdateSql(List<String> tableFields, String tableName,String where) {
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("update " + tableName + " set ");
        stringBuffer.append(joinStringBySplit(tableFields, "=?,"));
        stringBuffer.append("=?");
        stringBuffer.append(where);
        return stringBuffer.toString();
    }

    /**
     * 根据指定分隔符拼接字符串
     * @param strings 字符串集合
     * @param split 分隔符
     * @return
     */
    public static  String joinStringBySplit(List<String> strings, String split) {
        return strings.stream().reduce((x, y) -> x + split + y).orElse(null);
    }

    /**
     * 生成指定数量的占位符
     * @param cnt 数目
     * @return
     */
    public static  String getQuestionMark(int cnt) {
        StringBuilder stringBuffer = new StringBuilder();
        for (int i = 0; i < cnt; i++) {
            if (i == 0) {
                stringBuffer.append("?");
            } else {
                stringBuffer.append(",?");
            }
        }
        return stringBuffer.toString();
    }


    /**
     * 根据流程配置id，获取流程表id
     *
     * @param workFlowId 流程配置id
     * @return
     */
    public static  int getFlowTableLableId(String workFlowId) {
        RecordSet rs = new RecordSet();
        rs.execute("select w.formid from workflow_base w where w.id=" + workFlowId);
        rs.next();
        return rs.getInt("formid");
    }

    /**
     * 根据流程配置id，获取流程表名
     *
     * @param workFlowId 流程配置id
     * @return
     */
    public static  String getFlowTableName(String workFlowId) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select b.tablename from workflow_bill b where b.id=(select w.formid from workflow_base w where w.id=" + workFlowId + ")");
        rs.next();
        return rs.getString("tablename");
    }

    /**
     * 根据流程主表id获取流程详细表名称列表
     *
     * @param maindId
     * @return
     */
    public static  List<String> getDetailTables(int maindId) {
        List<String> resList = new ArrayList<>();
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT d.tablename FROM workflow_billdetailtable d where d.billid=" + maindId + ";");
        while (rs.next()) {
            resList.add(rs.getString("tablename"));
        }
        return resList;
    }


    /**
     * 根据表明查询配置表的id
     *
     * @param tableName
     * @return
     */
    public static  int getFlowTableIdByName(String tableName) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select id from workflow_bill w where w.tablename='" + tableName + "';");
        rs.next();
        return rs.getInt("id");
    }


    /**
     * 根据表id获取表字段
     *
     * @param tableId
     * @return
     */
    public static  List<String> getTableFileds(int tableId) {
        List<String> resList = new ArrayList<>();
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT f.fieldname FROM workflow_billfield f where f.viewtype=0 and  f.billid=" + tableId + ";");
        while (rs.next()) {
            resList.add(rs.getString("fieldname"));
        }
        return resList;
    }

    /**
     * 根据表id获取表字段
     *
     * @param tableId
     * @return
     */
    public static  List<String> getTableFileds(int tableId,String detailTableName) {
        List<String> resList = new ArrayList<>();
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT f.fieldname FROM workflow_billfield f where f.detailtable='"+detailTableName+"' and  f.billid=" + tableId + ";");
        while (rs.next()) {
            resList.add(rs.getString("fieldname"));
        }
        return resList;
    }


    /**
     * 根据流程记录id查询流程当前节点名称(currentnodetype 0创建，1审批，2提交，3归档)
     * @param requestId
     * @return
     */
    public static  String[] getFlowNodeName(String requestId){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select n.nodename,r.currentnodetype from workflow_requestbase r\n" +
                "left join workflow_nodebase  n on r.currentnodeid=n.id\n" +
                "where r.requestid="+requestId+";");
        rs.next();
        String nodeName= rs.getString("nodename");
        String currentnodetype=rs.getString("currentnodetype");
        return new String[]{currentnodetype,nodeName};
    }
}
